﻿Imports Microsoft.VisualBasic
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports BusinessRule.DTO
Imports System.Text
Imports System.Configuration
Imports DatabaseAccess.DBUtility

Public Class uMES_MaterialSetupBusiness
    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()

    Public Function GetMainTableNew(ByVal dto As MaterialParamterDto, ByVal PageIndex As Integer, ByVal PageSize As Integer) As uMESProcessResult
        Dim commBusiness As uMESCommonBusiness = New uMESCommonBusiness()
        Dim dispBusiness As uMESDispatchBusiness = New uMESDispatchBusiness()
        Dim strContainerName As String = ""
        Dim strTogetherFlag As String = ""
        Dim strProductName As String = ""
        If dto.strContainerName.Trim() <> "" Then
            strContainerName = dto.strContainerName.Trim()
            Dim r1 As uMESProcessResult = dispBusiness.GetContainerInfo(strContainerName)
            If r1.Result = True Then
                Try
                    strTogetherFlag = r1.DBTable.Rows(0)("TogetherFlag").ToString
                    strProductName = r1.DBTable.Rows(0)("productname").ToString
                Catch ex As Exception
                    strTogetherFlag = ""
                End Try
            End If
        End If

        Dim strWorkFlowName As String = ConfigurationSettings.AppSettings("DefaultMetalFlowName") '流程名称

        Dim strSql As String = "SELECT ws.workflowstepname,sb.specname,s.roleid,ws.workflowid,wb.workflowname,ws.sequence,r.rolename  FROM workflowstep ws " & vbCrLf & _
                               "LEFT JOIN workflow w ON w.workflowid=ws.workflowid " & vbCrLf & _
                               "LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid " & vbCrLf & _
                               "LEFT JOIN specbase sb ON sb.specbaseid =ws.specbaseid " & vbCrLf & _
                               "LEFT JOIN spec s ON s.specid =sb.revofrcdid " & vbCrLf & _
                               "LEFT JOIN roledef r ON r.roleid=s.roleid " & vbCrLf & _
                               "WHERE wb.workflowname='" & strWorkFlowName & "' ORDER BY ws.sequence"

        Dim workFlowTable As DataTable = dal.StandardGetDataTable(strSql)
        Dim strNextRoleID As String = ""
        Dim strRoleName As String = ""
        Dim strRoleID As String = ""

        Dim sql As String = "SELECT Distinct DECODE(c.containertype,7,NULL,c.containerid) facontainerid,c.containertype,DECODE(c.containertype,7,NULL,c.containername) containername,c.TogetherFlag,DECODE(c.containertype,7,NULL,c.containerid) containerid,c.mfgworkflowid,pb.productname,p.productrevision,c.productid,p.description,f.factoryname,sb.specname," & vbCrLf & _
                            "s.specrevision,ws.sequence, ws.workflowstepname,ws.islaststep,cts.specid,cts.workflowstepid,cl.containerlevelname," & vbCrLf & _
                            "c.qty,to_date(DECODE(c.containertype,7,NULL,cts.lastmovedate)) lastmovedate,c.isselfmfg,c.materialrequirementdate,c.plannedstartdate, s.roleid,to_date(DECODE(c.containertype,7,NULL,c.factorystartdate)) factorystartdate, " & vbCrLf & _
                            "(CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold,ut3.attributevalue as CheckTechnique,ut.attributevalue as printType,ut2.attributevalue AS BeginStep " & vbCrLf & _
                            "FROM container c " & vbCrLf & _
                            "LEFT JOIN containerlevel cl ON cl.containerlevelid = c.levelid " & vbCrLf & _
                            "LEFT JOIN product p ON p.productid = c.productid " & vbCrLf & _
                            "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid " & vbCrLf & _
                            "LEFT JOIN currentstatus cts ON cts.currentstatusid = c.currentstatusid " & vbCrLf & _
                            "LEFT JOIN spec s ON s.specid = cts.specid " & vbCrLf & _
                            "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid " & vbCrLf & _
                            "LEFT JOIN workflowstep ws ON ws.workflowstepid = cts.workflowstepid " & vbCrLf & _
                            "LEFT JOIN workflow w on w.workflowid=ws.workflowid " & vbCrLf & _
                            "LEFT JOIN cdodefinition cdo on cdo.cdodefid=w.cdotypeid " & vbCrLf & _
                            "LEFT JOIN factory f ON f.factoryid = cts.factoryid " & vbCrLf & _
                            "LEFT JOIN userattribute ut ON ut.parentid=c.containerid AND ut.userattributename='printType'" & vbCrLf & _
                            "LEFT JOIN userattribute ut2 ON ut2.parentid=c.containerid AND ut2.userattributename='BeginStep' " & vbCrLf & _
                            "LEFT JOIN userattribute ut3 ON ut3.parentid=c.containerid AND ut3.userattributename='CheckTechnique' " & vbCrLf & _
                            "WHERE c.status = 1 AND c.containername <> c.containerid AND c.parentcontainerid IS NULL " & vbCrLf & _
                             "AND ws.workflowstepname IS NOT NULL and cdo.cdoname='Workflow' " & vbCrLf & _
                            "AND s.roleid IN ( " & vbCrLf & _
                            "SELECT em.roleid FROM employeerole em LEFT JOIN employee e ON e.employeeid = em.employeeid WHERE LOWER(e.employeename) = '" & dto.strEmployeeName.ToLower() & "'" & vbCrLf & _
                            " ) "
        If Not String.IsNullOrWhiteSpace(dto.StrMaterialStep.Trim) Then
            If dto.IsMaterialStep Then
                sql += " AND WS.WORKFLOWSTEPNAME='" + dto.StrMaterialStep + "'"
            Else
                sql += " AND WS.WORKFLOWSTEPNAME<>'" + dto.StrMaterialStep + "'"
            End If
        End If

        If dto.IsEmp = True Then
            sql += " AND ut3.attributevalue= '" & dto.strEmployeeName & "' "
        End If

        If strTogetherFlag.Trim() <> "" Then
            sql += "AND c.TogetherFlag= '" & strTogetherFlag & "'" & vbCrLf
            sql += "AND LOWER(pb.productname)='" & strProductName.ToLower & "'" & vbCrLf
        Else
            If dto.strContainerName.Trim() <> "" Then
                sql += "AND LOWER(c.containername) LIKE '%" & strContainerName.ToLower() & "%'" & vbCrLf
            End If
        End If

        ''产品图号
        If dto.ProductName.Trim <> "" Then
            sql += "AND LOWER(pb.productname) LIKE '%" & dto.ProductName.ToLower & "%'" & vbCrLf
        End If

        ''车间，如果有“车间”两个字则过滤，否则不过滤
        Dim strFactoryName As String = dto.FactoryName
        If strFactoryName <> "" Then  'update:Wangjh 2017-6-28 去除 And strFactoryName.Contains("车间") 
            sql += "AND f.factoryname = '" & dto.FactoryName & "'" & vbCrLf
        End If

        If dto.strStartDate.Trim() <> "" Then
            sql += "AND c.plannedstartdate >= TO_DATE('" & dto.strStartDate & " 00:00:00','yyyy-mm-dd hh24:mi:ss')" & vbCrLf
        End If

        If dto.strEndDate.Trim() <> "" Then
            sql += "AND c.plannedstartdate <= TO_DATE('" & dto.strEndDate & " 23:59:59','yyyy-mm-dd hh24:mi:ss')" & vbCrLf
        End If
        sql += "ORDER BY c.togetherflag" & vbCrLf

        Dim para As uMESPagingDataDTO = New uMESPagingDataDTO()
        para.CurrentPageIndex = PageIndex
        para.PageSize = PageSize
        para.strSQL = sql
        Dim result As uMESProcessResult = dal.RunProcedureForPage(para)
        Dim tmpTable As DataTable = result.DBTable.Copy()

        Dim strContianerIDList As String = ""
        For Each row As DataRow In tmpTable.Rows
            strContianerIDList = strContianerIDList + "'" + row("containerid").ToString().Trim() + "',"
        Next
        If strContianerIDList.Trim() <> "" Then
            strContianerIDList = strContianerIDList.Substring(0, strContianerIDList.Length - 1)
        Else
            Return result
        End If

        sql = "SELECT c.containername,t.attributevalue,t.userattributename,t.parentid FROM Userattribute t LEFT JOIN container c ON c.containerid=t.parentid WHERE t.parentid IN (" & strContianerIDList & ")"

        'sql = "SELECT d.dataname,d.datavalue,c.containername,h.txndate  FROM Datapointhistory t " & vbCrLf & _
        '      "LEFT JOIN Datapointhistorydetail d ON d.datapointhistoryid=t.datapointhistoryid " & vbCrLf & _
        '      "LEFT JOIN historymainline h ON h.historymainlineid=t.historymainlineid " & vbCrLf & _
        '      "LEFT JOIN container c ON c.containerid=h.containerid " & vbCrLf & _
        '      "WHERE h.reversalstatus=1 AND  c.containerid IN (" & strContianerIDList & ")  ORDER BY h.txndate DESC"

        Dim dataPointTable As DataTable = dal.StandardGetDataTable(sql)

        tmpTable.Columns.Add("实发数/顺序号")
        tmpTable.Columns.Add("熔炉号子")
        tmpTable.Columns.Add("熔炉号母")
        tmpTable.Columns.Add("检验编号")
        tmpTable.Columns.Add("代料牌号")
        tmpTable.Columns.Add("代料规格")
        tmpTable.Columns.Add("代料单号")
        tmpTable.Columns.Add("平行作业工序")
        tmpTable.Columns.Add("平行作业单号")
        tmpTable.Columns.Add("canMove")

        For Each row As DataRow In tmpTable.Rows

            Dim strFilter As String = String.Format("parentid='{0}' and userattributename='实发数/顺序号' ", row("containerid").ToString().Trim())
            Dim tmpRows As DataRow() = dataPointTable.Select(strFilter)
            If tmpRows.Length > 0 Then
                If tmpRows(0).IsNull("attributevalue") = False Then
                    row("实发数/顺序号") = tmpRows(0)("attributevalue")
                End If
            End If
            strFilter = String.Format("parentid='{0}' and userattributename='熔炉号子' ", row("containerid").ToString().Trim())
            tmpRows = dataPointTable.Select(strFilter)
            If tmpRows.Length > 0 Then
                If tmpRows(0).IsNull("attributevalue") = False Then
                    row("熔炉号子") = tmpRows(0)("attributevalue")
                End If
            End If
            strFilter = String.Format("parentid='{0}' and userattributename='熔炉号母' ", row("containerid").ToString().Trim())
            tmpRows = dataPointTable.Select(strFilter)
            If tmpRows.Length > 0 Then
                If tmpRows(0).IsNull("attributevalue") = False Then
                    row("熔炉号母") = tmpRows(0)("attributevalue")
                End If
            End If

            strFilter = String.Format("parentid='{0}' and userattributename='检验编号' ", row("containerid").ToString().Trim())
            tmpRows = dataPointTable.Select(strFilter)
            If tmpRows.Length > 0 Then
                If tmpRows(0).IsNull("attributevalue") = False Then
                    row("检验编号") = tmpRows(0)("attributevalue")
                End If
            End If

            strFilter = String.Format("parentid='{0}' and userattributename='代料牌号' ", row("containerid").ToString().Trim())
            tmpRows = dataPointTable.Select(strFilter)
            If tmpRows.Length > 0 Then
                If tmpRows(0).IsNull("attributevalue") = False Then
                    row("代料牌号") = tmpRows(0)("attributevalue")
                End If
            End If

            strFilter = String.Format("parentid='{0}' and userattributename='代料规格' ", row("containerid").ToString().Trim())
            tmpRows = dataPointTable.Select(strFilter)
            If tmpRows.Length > 0 Then
                If tmpRows(0).IsNull("attributevalue") = False Then
                    row("代料规格") = tmpRows(0)("attributevalue")
                End If
            End If

            strFilter = String.Format("parentid='{0}' and userattributename='代料单号' ", row("containerid").ToString().Trim())
            tmpRows = dataPointTable.Select(strFilter)
            If tmpRows.Length > 0 Then
                If tmpRows(0).IsNull("attributevalue") = False Then
                    row("代料单号") = tmpRows(0)("attributevalue")
                End If
            End If

            strFilter = String.Format("parentid='{0}' and userattributename='平行作业工序' ", row("containerid").ToString().Trim())
            tmpRows = dataPointTable.Select(strFilter)
            If tmpRows.Length > 0 Then
                If tmpRows(0).IsNull("attributevalue") = False Then
                    row("平行作业工序") = tmpRows(0)("attributevalue")
                End If
            End If

            strFilter = String.Format("parentid='{0}' and userattributename='平行作业单号' ", row("containerid").ToString().Trim())
            tmpRows = dataPointTable.Select(strFilter)
            If tmpRows.Length > 0 Then
                If tmpRows(0).IsNull("attributevalue") = False Then
                    row("平行作业单号") = tmpRows(0)("attributevalue")
                End If
            End If
        Next
        '子表 单件单卡 add:Wangjh 2017-9-1 改为父子表
        Dim dv As DataView = tmpTable.DefaultView
        dv.RowFilter = "containertype='7'"

        Dim containerflag As String = ""
        For Each drv As DataRowView In dv
            containerflag += String.Format("'{0}',", drv("TOGETHERFLAG"))
        Next
        If containerflag.Length > 0 Then
            containerflag = containerflag.Substring(0, containerflag.Length - 1)
        Else
            containerflag += String.Format("'{0}'", containerflag)
        End If

        Dim sonSql As String = " SELECT c.containername,c.containerid,c.productid,c.containertype,cu.workflowstepid,c.togetherflag,cu.lastmovedate,c.factorystartdate,c.isselfmfg,ut2.attributevalue BeginStep FROM container c " &
                                " LEFT JOIN Currentstatus cu ON cu.currentstatusid=c.currentstatusid" &
                                " LEFT JOIN workflowstep ws ON ws.workflowstepid=cu.workflowstepid" &
                                "  LEFT JOIN workflow w ON w.workflowid=ws.workflowid" &
                                " LEFT JOIN cdodefinition cdo ON cdo.cdodefid=w.cdotypeid" &
                                "  LEFT JOIN userattribute ut2 ON ut2.parentid=c.containerid AND ut2.userattributename='BeginStep' " &
                                " where c.containertype=7 AND cdo.cdoname='Workflow'"
        sonSql += String.Format(" AND c.togetherflag IN ({0})", containerflag)
        sonSql += " ORDER BY c.containername"
        Dim sonContainerDt As New DataTable
        sonContainerDt = DatabaseHelper.Query_ReturnDatTable(sonSql)
        sonContainerDt.Columns.Add("FaContainerID")



        Dim ds As New DataSet
        ds.Tables.Add(tmpTable)
        ds.Tables(0).TableName = "sumContainer"
        ds.Tables.Add(sonContainerDt)
        ds.Tables(1).TableName = "sonContainer"
        ds.Relations.Add("SumContainer",
        New DataColumn() {ds.Tables(0).Columns("FaContainerID"), ds.Tables(0).Columns("PRODUCTID"), ds.Tables(0).Columns("TOGETHERFLAG"),
                          ds.Tables(0).Columns("CONTAINERTYPE"), ds.Tables(0).Columns("WORKFLOWSTEPID")},
        New DataColumn() {ds.Tables(1).Columns("FaContainerID"), ds.Tables(1).Columns("PRODUCTID"), ds.Tables(1).Columns("TOGETHERFLAG"),
                          ds.Tables(1).Columns("CONTAINERTYPE"), ds.Tables(1).Columns("WORKFLOWSTEPID")})

        result.DBDataSet = ds

        Return result
    End Function


    Public Function GetDataCollectInfo(ByVal containerNameList As List(Of String)) As DataTable
        Dim strContianerIDList As String = ""
        For i As Integer = 0 To containerNameList.Count - 1
            strContianerIDList = strContianerIDList + "'" + containerNameList(i).Trim() + "',"
        Next
        If strContianerIDList.Trim() <> "" Then
            strContianerIDList = strContianerIDList.Substring(0, strContianerIDList.Length - 1)
        Else
            strContianerIDList = "'$$$$'"
        End If

        Dim Sql As String = "SELECT c.containername,t.attributevalue,t.userattributename,t.parentid FROM Userattribute t LEFT JOIN container c ON c.containerid=t.parentid WHERE c.containername IN (" & strContianerIDList & ")"
        Dim dataPointTable As DataTable = dal.StandardGetDataTable(Sql)
        Return dataPointTable
    End Function


    '判断最后一步操作是否召回
    Public Function GetZhaohuiContainerByStep(ByVal containerid As String) As String
        Dim result As String = ""
        Dim strQuery As String = "select movehistory.historymainlineid,workflowstep.workflowstepname,cdodefinition.cdoname,container.containername,container.containerid," & vbCrLf & _
                                "historymainline.historymainlineid,employee.fullname,cdo.cdoname " & vbCrLf & _
                                "from movehistory" & vbCrLf & _
                                "inner join historymainline on historymainline.historymainlineid=movehistory.historymainlineid" & vbCrLf & _
                                "inner join container on container.containerid=historymainline.containerid" & vbCrLf & _
                                "left  join currentstatus cts ON cts.currentstatusid = container.currentstatusid" & vbCrLf & _
                                "left join workflowstep ON workflowstep.workflowstepid = historymainline.workflowstepid " & vbCrLf & _
                                "left join cdodefinition on cdodefinition.cdodefid=historymainline.txntype " & vbCrLf & _
                                "left join employee on employee.employeeid=historymainline.employeeid" & vbCrLf & _
                                "left join workflow on workflow.workflowid=workflowstep.workflowid" & vbCrLf & _
                                "left join cdodefinition cdo on cdo.cdodefid=workflow.cdotypeid" & vbCrLf & _
                                "where cdo.cdoname='Workflow' and container.containerid='" + containerid + "'" & vbCrLf & _
                                "order by historymainline.txndate desc" & vbCrLf

        Dim dt As DataTable = dal.GetContainerInfo(strQuery)
        If dt.Rows.Count = 0 Then
            Return "0"
        Else
            If dt.Rows.Count > 1 Then
                For i = 0 To dt.Rows.Count - 1
                    If dt.Rows(i).Item("cdoname").ToString() = "MoveStd" Then
                        result = "0"
                        Exit For
                    ElseIf dt.Rows(i).Item("cdoname").ToString() = "MoveNonStd" Then
                        If dt.Rows(i).Item("fullname").ToString() = dt.Rows(i + 1).Item("fullname").ToString() Then
                            result = "1"
                            Exit For
                        End If
                    End If
                Next
            End If
        End If
        Return result
    End Function

    Public Function GetStepByToStep(ByVal strContainerName As String, ByVal strToStepID As String) As String
        Dim strQuery As String = "SELECT wfs.workflowstepname" & vbCrLf & _
                                "FROM movehistory mh" & vbCrLf & _
                                "LEFT JOIN historymainline hml ON hml.historymainlineid = mh.historymainlineid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = hml.containerid" & vbCrLf & _
                                "LEFT JOIN cdodefinition cdo ON cdo.cdodefid = hml.txntype" & vbCrLf & _
                                "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = mh.stepid" & vbCrLf & _
                                "WHERE cdo.cdoname = 'MoveStd' AND hml.reversalstatus = 1" & vbCrLf & _
                                "AND c.containername = '" & strContainerName & "'" & vbCrLf & _
                                "AND mh.tostepid = '" & strToStepID & "'" & vbCrLf

        Dim DT As DataTable = dal.GetContainerInfo(strQuery)

        If DT.Rows.Count = 0 Then
            Return ""
        Else
            Return DT.Rows(0)("WorkflowStepName").ToString
        End If
    End Function

    Public Function GetMaterialTypeByContainer(ByVal strContainer As String) As String

        Dim sql As String = "select t.materialtypename from container c "
        sql += " inner join product p on p.productid=c.productid "
        sql += " inner join materialtype t on t.materialtypeid=p.materialtypeid "
        sql += " where c.containername='" + strContainer + "'"

        Dim dt As DataTable = dal.GetContainerInfo(sql)

        If dt.Rows.Count = 0 Then
            GetMaterialTypeByContainer = ""
        Else
            GetMaterialTypeByContainer = dt.Rows(0)(0).ToString()
        End If


    End Function

    Public Function GetProductDetail(ByVal productID As String) As DataTable
        Dim sql As String =
            String.Format("select t.materialsize,t.materialspecification,t.materialstatus,t.materialtrademark," +
                          "t.technicalconditions,m.materialtypename,t.MaterialName from product t " +
                          "left join materialtype m on m.materialtypeid=t.materialtypeid " +
                          "where t.productid='{0}'", productID)
        GetProductDetail = dal.GetContainerInfo(sql)
    End Function

    Public Function GetMaterialDetail(ByVal MaterialName As String) As DataTable
        Dim sql As String =
            String.Format("select t.furnacenumber,t.materialcontainer,t.materialmfgdate,t.materialmfgdepartment," +
                          "t.materialname,t.materialsize,t.materialspecification,t.materialtrademark,t.qualificationnum," +
                          "t.repeatcheckdate,t.storeddate,t.hotprocessstatus from materialinfo t " +
                          "where t.materialinfoname='{0}'", MaterialName)

        GetMaterialDetail = dal.GetContainerInfo(sql)

    End Function

    Public Function GetMaterialTechnicalconditions(ByVal MaterialName As String) As DataTable
        Dim sql As String = String.Format("select mt.materialinfoname, m.*,t.* from MaterialInfoTechnicalCondition m " +
                                          "left join  technicalconditions t on t.technicalconditionsid=m.technicalconditionsid " +
                                          "left join materialinfo mt on mt.materialinfoid=m.materialinfoid where mt.materialinfoname='{0}'", MaterialName)
        GetMaterialTechnicalconditions = dal.GetContainerInfo(sql)
    End Function

    Public Function GetMaterialStateByMaterialInfoName(ByVal strMaterialInfoName As String) As DataTable
        Dim strQuery As String = "SELECT ms.materialstatename FROM materialstate ms" & vbCrLf & _
                                "LEFT JOIN materialinfomaterialstate mm ON mm.materialstateid = ms.materialstateid" & vbCrLf & _
                                "LEFT JOIN materialinfo mi ON mi.materialinfoid = mm.materialinfoid" & vbCrLf & _
                                "WHERE mi.materialinfoname = '" & strMaterialInfoName & "'" & vbCrLf

        Return dal.GetContainerInfo(strQuery)
    End Function


    Public Function GetUserDataCollectionByContainer(ByVal containerid As String) As DataTable
        Dim sql As String =
            String.Format("select  c.containerid,hm.txndate,d.datapointname as dataname,t.datavalue||u.uomname as datavalue,sb.specname,hm.employeeid,hm.userid " +
                          "from container c  " +
                          "inner join historymainline hm on hm.containerid = c.containerid " +
                          "inner join datapointhistory dh on dh.historymainlineid = hm.historymainlineid " +
                          "inner join datapointhistorydetail t on t.datapointhistoryid = dh.datapointhistoryid " +
                          "inner join datapoint d on d.datapointid = t.datapointid " +
                          "left join uom u on u.uomid=d.uomid  " +
                          "inner join (select case when wfs.specid = '0000000000000000' then wfs.specbaseid else specid end as specid,wfs.workflowstepid from workflowstep wfs  " +
                          ") temp on temp.workflowstepid = hm.workflowstepid " +
                          "inner join spec s on temp.specid = s.specid " +
                          "inner join specbase sb on sb.specbaseid = s.specbaseid " +
                          "inner join " +
                          "(select historymainline.containerid,historymainline.workflowstepid,max(historymainline.txndate) as txndate from datapointhistory  " +
                          "inner join historymainline on historymainline.historymainlineid = datapointhistory.historymainlineid " +
                          "group by historymainline.containerid,historymainline.workflowstepid " +
                          ") temp2 on temp2.containerid = c.containerid and temp2.workflowstepid = hm.workflowstepid and temp2.txndate = hm.txndate " +
                          "where c.containerid = '{0}' " +
                          "order by sb.specname", containerid)

        GetUserDataCollectionByContainer = dal.GetContainerInfo(sql)

    End Function

    Public Function GetOperationEmployee(ByVal stepname As String, ByVal workflowstepid As String, ByVal strFactoryName As String) As DataTable
        Dim sql As String = "SELECT DISTINCT t.fullname,t.employeename" & vbCrLf & _
                            "FROM employee t" & vbCrLf & _
                            "LEFT JOIN sessionvalues sv ON sv.employeeid=t.employeeid" & vbCrLf & _
                            "LEFT JOIN employeerole em ON em.employeeid=t.employeeid" & vbCrLf & _
                            "LEFT JOIN spec s ON s.roleid=em.roleid" & vbCrLf & _
                            "LEFT JOIN factory f ON f.factoryid = sv.factoryid" & vbCrLf & _
                            "LEFT JOIN workflowstep ws ON" & vbCrLf & _
                            "   (s.specid=ws.specid AND  ws.specbaseid='0000000000000000')" & vbCrLf & _
                            "   OR ( ws.specid='0000000000000000' and  s.specbaseid=ws.specbaseid)" & vbCrLf & _
                            "LEFT JOIN workflowstep wse ON wse.workflowid=ws.workflowid" & vbCrLf & _
                            "WHERE ws.workflowstepname='" & stepname & "'" & vbCrLf & _
                            "AND wse.workflowstepid='" & workflowstepid & "'" & vbCrLf

        If stepname = "工段长接收" Then
            sql += "AND f.factoryname = '" & strFactoryName & "'" & vbCrLf
        End If

        GetOperationEmployee = dal.GetContainerInfo(sql)

    End Function

    Public Function IsLastStepContainer(ByVal containername As String) As Boolean
        Dim sql As String =
    String.Format("select t.containername,cl.containerlevelname,ws.workflowstepname,ws.islaststep from container t " +
                  "left join containerlevel cl on cl.containerlevelid=t.levelid " +
                  "left join currentstatus cts on cts.currentstatusid=t.currentstatusid " +
                  "left join workflowstep ws on ws.workflowstepid=cts.workflowstepid " +
                  "where t.containername ='{0}' and ws.islaststep=1", containername)

        Dim dt As DataTable = dal.GetContainerInfo(sql)
        If dt IsNot Nothing And dt.Rows.Count > 0 Then
            IsLastStepContainer = True
        Else
            IsLastStepContainer = False
        End If
    End Function

    Public Function GetFirstWorkflowStep(ByVal workflowid As String) As DataTable
        Dim sql As String =
    String.Format("select t.workflowstepid,t.workflowstepname,w.workflowrevision,wb.workflowname from workflowstep t " +
                  "left join workflow w on w.workflowid=t.workflowid " +
                  "left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid " +
                  "where t.sequence = 1 And t.workflowid ='{0}' ", workflowid)

        GetFirstWorkflowStep = dal.GetContainerInfo(sql)

    End Function

    Public Function GetOperationPath(ByVal stepID As String) As DataTable
        Dim sql As String =
    String.Format("select distinct p.fromstepid,p.tostepid,w.islaststep,s.specid||'|'||s.specrevision spec, sb.specname,w.workflowstepname,p.pathname,(case when p.pathid = w2.defaultpathid then 1 else 0 end) as isdefaultpath from path p " +
                  "left join workflowstep w on w.workflowstepid = p.tostepid " +
                  "left join workflowstep w2 on w2.workflowstepid = p.fromstepid " +
                  "left join spec s on (s.specid=w.specid and  w.specbaseid='0000000000000000') " +
                  "or ( w.specid='0000000000000000' and  s.specbaseid=w.specbaseid) " +
                  "left join specbase sb on sb.specbaseid = s.specbaseid " +
                  "where p.fromstepid='{0}' " +
                  "order by isdefaultpath desc ", stepID)

        GetOperationPath = dal.GetContainerInfo(sql)

    End Function

    Public Function GetPreWorkflowStep(ByVal stepID As String) As DataTable
        Dim sql As String =
    String.Format("select distinct p.fromstepid,p.tostepid,wfs.islaststep,s.specid||'|'||s.specrevision spec, sb.specname,wfs.workflowstepname,p.pathname,wf.workflowid,wfb.workflowname,wf.workflowrevision from path p " +
                  "left join workflowstep wfs on wfs.workflowstepid = p.fromstepid  " +
                  "left join workflow wf on wf.workflowid = wfs.workflowid  " +
                  "left join workflowbase wfb on wfb.workflowbaseid = wf.workflowbaseid " +
                  "left join spec s on (s.specid=wfs.specid and  wfs.specbaseid='0000000000000000') " +
                  "or ( wfs.specid='0000000000000000' and  s.specbaseid=wfs.specbaseid) " +
                  "left join specbase sb on sb.specbaseid = s.specbaseid " +
                  "where p.tostepid='{0}'", stepID)

        GetPreWorkflowStep = dal.GetContainerInfo(sql)
    End Function

    Public Function GetWorkflowByStep(ByVal stepid As String) As String

        Dim sql As String =
    String.Format("select t.workflowstepid, t.workflowstepname,w.workflowrevision,wb.workflowname from workflowstep t " +
                  "left join workflow w on w.workflowid =t.workflowid " +
                  "left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid " +
                  "where t.workflowstepid='{0}'", stepid)

        Dim dt As DataTable = dal.GetContainerInfo(sql)
        If dt IsNot Nothing And dt.Rows.Count > 0 Then
            GetWorkflowByStep = String.Format("{0}|{1}", dt.Rows(0)("workflowname"), dt.Rows(0)("workflowrevision"))
        Else
            GetWorkflowByStep = String.Empty
        End If

    End Function

    Public Function GetContainerChooser(ByVal containerid As String, ByVal specid As String) As String

        Dim sql As String =
    String.Format("select t.containerchoosername from containerchooser t " +
                  "where t.containerid ='{0}' and t.specid='{1}'", containerid, specid)

        Dim dt As DataTable = dal.GetContainerInfo(sql)
        If dt IsNot Nothing And dt.Rows.Count > 0 Then
            GetContainerChooser = dt.Rows(0)("containerchoosername")
        Else
            GetContainerChooser = String.Empty
        End If

    End Function

    Public Function GetTaskInfo(ByVal inventtaskinfoid As String) As DataTable
        Dim sql As String =
    String.Format("select task.inventtaskinfoname as taskname,task.planbegindate,task.planenddate,task.qty,task.taskstatus,task.tasktype,task.urgent " +
                  "from inventtaskinfo task " +
                  "where task.inventtaskinfoid = '{0}'", inventtaskinfoid)

        GetTaskInfo = dal.GetContainerInfo(sql)

    End Function

    Public Function GetCurrentStepByRole(ByVal dto As MaterialParamterDto) As DataTable
        Dim sql As String =
    String.Format("select distinct s.specid,sb.specname,cts.workflowstepid,wfs.workflowstepname,wfs.workflowid,wfs.workflowname,wfs.workflowrevision  " +
                  "from spec s " +
                  "left join specbase sb on sb.specbaseid = s.specbaseid " +
                  "left join currentstatus cts on cts.specid = s.specid " +
                  "inner join (select case when workflowstep.workflowstepid = '0000000000000000' then workflowstep.workflowstepid else workflowstep.workflowstepid end as workflowstepid, workflowstep.workflowstepname,workflowstep.workflowid,wfb.workflowname,wf.workflowrevision " +
                  "from workflowstep " +
                  "left join workflow wf on wf.workflowid = workflowstep.workflowid " +
                  "left join workflowbase wfb on wfb.workflowbaseid = wf.workflowbaseid " +
                  ")  wfs on wfs.workflowstepid = cts.workflowstepid " +
                  "where s.roleid in (select em.roleid from employee e left join employeerole em on em.employeeid=e.employeeid left join roledef rf on rf.roleid = em.roleid " +
                  "where lower(e.employeename)='{0}')", dto.strEmployeeName.ToLower())

        GetCurrentStepByRole = dal.GetContainerInfo(sql)
    End Function

    ''获取当前在领料流程的所有批次
    Public Function GetMaterialContainer(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sql As String = "SELECT c.containername" & vbCrLf & _
                            "FROM container c" & vbCrLf & _
                            "LEFT JOIN prioritycode p ON p.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                            "LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                            "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid" & vbCrLf & _
                            "LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid" & vbCrLf & _
                            "LEFT JOIN cdodefinition cdo ON cdo.cdodefid = wf.cdotypeid" & vbCrLf & _
                            "WHERE cdo.cdoname = 'Workflow'" & vbCrLf & _
                            "AND c.parentcontainerid IS NULL" & vbCrLf & _
                            "AND c.containerid <> c.containername" & vbCrLf

        If para.ContainsKey("ContainerName") Then
            sql += "AND LOWER(c.containername) LIKE '%" & para("ContainerName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.ContainsKey("PriorityCode") Then
            sql += "AND LOWER(p.prioritycodename) = '" & para("PriorityCode").ToString.ToLower & "'" & vbCrLf
        End If

        Dim DT As DataTable = dal.GetContainerInfo(sql)
        Return DT
    End Function

    ''获取当前在领料流程的所有批次的操作记录
    Public Function GetMaterialContainerOprHistory(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sql As String = "SELECT c.containername,m.mfgordername,pb.productname,p.description,p.materialspecification,p.materialsize," & vbCrLf & _
                            "   f.factoryname,c.originalqty,c.qty,wfs2.workflowstepname AS currentstep,pc.prioritycodename,wfb.workflowname," & vbCrLf & _
                            "   wf.workflowrevision,wfs.workflowstepname AS prevstep,hml.txndate,c.plannedstartdate,c.plannedcompletiondate," & vbCrLf & _
                            "   hml.txntype,e.employeename" & vbCrLf & _
                            "FROM historymainline hml" & vbCrLf & _
                            "LEFT JOIN container c ON c.containerid = hml.containerid" & vbCrLf & _
                            "LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                            "LEFT JOIN mfgorder m ON m.mfgorderid = c.mfgorderid" & vbCrLf & _
                            "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                            "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                            "LEFT JOIN factory f ON f.factoryid = c.originalfactoryid" & vbCrLf & _
                            "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = hml.workflowstepid" & vbCrLf & _
                            "LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid" & vbCrLf & _
                            "LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid" & vbCrLf & _
                            "LEFT JOIN cdodefinition cdo ON cdo.cdodefid = wf.cdotypeid" & vbCrLf & _
                            "LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                            "LEFT JOIN workflowstep wfs2 ON wfs2.workflowstepid = cu.workflowstepid" & vbCrLf & _
                            "LEFT JOIN workflow wf2 ON wf2.workflowid = wfs2.workflowid" & vbCrLf & _
                            "LEFT JOIN cdodefinition cdo2 ON cdo2.cdodefid = wf2.cdotypeid" & vbCrLf & _
                            "LEFT JOIN employee e ON e.employeeid = hml.employeeid" & vbCrLf & _
                            "WHERE hml.reversalstatus = 1 AND c.parentcontainerid IS NULL" & vbCrLf & _
                            "AND cdo.cdoname = 'Workflow' AND cdo2.cdoname = 'Workflow'" & vbCrLf & _
                            "AND c.containerid <> c.containername" & vbCrLf

        If para.ContainsKey("ContainerName") Then
            sql += "AND LOWER(c.containername) LIKE '%" & para("ContainerName").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.ContainsKey("PriorityCode") Then
            sql += "AND LOWER(pc.prioritycodename) = '" & para("PriorityCode").ToString.ToLower & "'" & vbCrLf
        End If

        Dim DT As DataTable = dal.GetContainerInfo(sql)
        Return DT
    End Function

    Public Function GetNextStepGrid(ByVal dto As MaterialParamterDto, ByVal strSteps As String, ByVal fromSteps As String) As uMESProcessResult
        Dim sql As String =
      "select distinct o.mfgordername,c.containername,c.containerid,c.mfgworkflowid,pb.productname,p.productrevision,c.productid,p.description,f.factoryname,sb.specname, "
        sql += "s.specrevision, ws.workflowstepname,wsf.workflowstepname fromstep, e.employeename,cts.specid,cts.workflowstepid,cl.containerlevelname,"
        sql += " c.qty|| '('||u.uomname || ')' as qty,pr.prioritycodename,cts.lastmovedate,c.materialrequirementdate,c.plannedstartdate,task.inventsizeid,p.workflowroute,task.inventtaskinfoid,wf.workflowid "
        sql += " from container c "
        sql += " left join containerlevel cl on cl.containerlevelid=c.levelid "
        sql += " left join mfgorder o on o.mfgorderid=c.mfgorderid "
        sql += " left join inventtaskinfo task on task.inventtaskinfoid = o.inventtaskinfoid "
        sql += " left join uom u on u.uomid=c.uomid "
        sql += " left join prioritycode pr on pr.prioritycodeid=c.prioritycodeid "
        sql += " left join product p on p.productid=c.productid "
        sql += " left join productbase pb on pb.productbaseid=p.productbaseid "
        sql += " left join currentstatus cts on cts.currentstatusid=c.currentstatusid "
        sql += " left join spec s on s.specid=cts.specid "
        sql += " left join txnmap txn on txn.specid = s.specid "
        sql += " left join specbase sb on sb.specbaseid=s.specbaseid "
        sql += " left join workflowstep ws on ws.workflowstepid = cts.workflowstepid"
        sql += " left join workflow wf on wf.workflowid = ws.workflowid "
        sql += " left join factory f on f.factoryid=cts.factoryid "
        sql += " left join containerchooser cc on cc.containerid=c.containerid "
        sql += " left join workflowstep wsf  on wsf.specid = cc.specid and wsf.workflowid=ws.workflowid "
        sql += " left join employee e on e.employeeid= cc.assignedemployeeid "
        sql += "where cts.workflowstepid in (" + strSteps + ")"
        sql += " and wsf.workflowstepname in (" + fromSteps + ") "

        If dto.strContainerName.Trim() <> "" Then
            sql += " and c.containername like '%" + dto.strContainerName + "%' "
        End If

        If dto.strPriorityName.Trim() <> "" Then
            sql += " and pr.prioritycodename='" + dto.strPriorityName + "' "
        End If

        If dto.strStartDate.Trim() <> "" Then
            sql += " and c.plannedstartdate >= to_date('" + dto.strStartDate + " 00:00:00','yyyy-mm-dd hh24:mi:ss') "
        End If

        If dto.strEndDate.Trim() <> "" Then
            sql += " and c.plannedstartdate <= to_date('" + dto.strEndDate + " 23:59:59','yyyy-mm-dd hh24:mi:ss') "
        End If

        sql += " order by cts.lastmovedate desc "

        GetNextStepGrid = New uMESProcessResult()
        GetNextStepGrid.DBTable = dal.GetContainerInfo(sql)
    End Function

    Public Function GetNextPath(ByVal strSteps As String) As String
        Dim sql As String =
    String.Format("select distinct p.fromstepid,p.tostepid,w.islaststep,s.specid||'|'||s.specrevision spec, sb.specname,w.workflowstepname,p.pathname from path p " +
                  "left join workflowstep w on w.workflowstepid = p.tostepid " +
                  "left join spec s on (s.specid=w.specid and  w.specbaseid='0000000000000000') " +
                  "or ( w.specid='0000000000000000' and  s.specbaseid=w.specbaseid) " +
                  "left join specbase sb on sb.specbaseid = s.specbaseid " +
                  "where p.fromstepid in ({0})", strSteps)
        Dim dt As DataTable = dal.GetContainerInfo(sql)
        Dim result As String = "'$$$$$'"
        For Each dr As DataRow In dt.Rows
            result += ",'" + dr("tostepid").ToString().Trim() + "'"
        Next
        GetNextPath = result

    End Function

    '获取待打印的下料单列表'
    Public Function GetMaterialXialiaoList(ByVal dto As MaterialParamterDto, ByVal PageIndex As Integer, ByVal PageSize As Integer, ByRef containerids As String) As uMESProcessResult

        Dim sql As String = "SELECT DISTINCT Historymainline.Containerid,Container.Containername,Specbase.Specname, Employee.FullName,cdo1.cdoname,Historymainline.Txndate,Historymainline.Historymainlineid" & vbCrLf & _
                    "FROM Movehistory " & vbCrLf & _
                    "INNER JOIN Historymainline ON Movehistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                    "INNER JOIN" & vbCrLf & _
                    "(" & vbCrLf & _
                     "   SELECT Historymainline.Containerid, Historymainline.Workflowstepid, MAX(Historymainline.Txndate) AS Txndate" & vbCrLf & _
                    "    FROM Movehistory " & vbCrLf & _
                     "   INNER JOIN Historymainline ON Movehistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                     "   INNER JOIN Workflowstep ON Historymainline.Workflowstepid = Workflowstep.Workflowstepid" & vbCrLf & _
                     "   INNER JOIN" & vbCrLf & _
                      "  (" & vbCrLf & _
                     "       SELECT Container.Containerid, (CASE WHEN Cdodefinition.Cdoname = 'MachineMfgWorkFlow' THEN 9999 ELSE Workflowstep.Sequence END) AS SpecIndex" & vbCrLf & _
                      "      FROM Container" & vbCrLf & _
                       "     INNER JOIN Currentstatus ON Container.Currentstatusid= Currentstatus.Currentstatusid" & vbCrLf & _
                       "     INNER JOIN Workflowstep ON Currentstatus.Workflowstepid = Workflowstep.Workflowstepid" & vbCrLf & _
                       "     INNER JOIN Workflow ON Workflow.Workflowid = Workflowstep.Workflowid" & vbCrLf & _
                       "     INNER JOIN Cdodefinition ON Workflow.Cdotypeid = Cdodefinition.Cdodefid" & vbCrLf & _
                      "  ) CSpec ON CSpec.Containerid= Historymainline.ContainerID AND CSpec.SpecIndex >= Workflowstep.Sequence" & vbCrLf & _
                      "  WHERE Historymainline.reversalstatus = 1 " & vbCrLf & _
                     "   GROUP BY Historymainline.Containerid, Historymainline.Workflowstepid" & vbCrLf & _
                    ") CMax ON CMax.Containerid = Historymainline.Containerid AND CMax.Workflowstepid = Historymainline.Workflowstepid AND CMax.Txndate = Historymainline.Txndate" & vbCrLf & _
                    "INNER JOIN Employee ON Historymainline.Employeeid = Employee.Employeeid" & vbCrLf & _
                    "INNER JOIN" & vbCrLf & _
                    "(" & vbCrLf & _
                    "    SELECT Workflowstep.Workflowid, Workflowstep.Workflowstepid, " & vbCrLf & _
                    "           (CASE WHEN Workflowstep.Specid = '0000000000000000' THEN Specbase.Revofrcdid ELSE Spec.Specid END) AS Specid" & vbCrLf & _
                    "    FROM Workflowstep " & vbCrLf & _
                     "   LEFT OUTER JOIN Spec ON Workflowstep.Specid = Spec.Specid" & vbCrLf & _
                     "   LEFT OUTER JOIN Specbase ON Specbase.Specbaseid = Workflowstep.Specbaseid" & vbCrLf & _
                    ") CStep ON Movehistory.Stepid = CStep.Workflowstepid" & vbCrLf & _
                    "INNER JOIN Workflow ON Workflow.Workflowid = CStep.Workflowid" & vbCrLf & _
                    "INNER JOIN Cdodefinition ON Cdodefinition.Cdodefid = Workflow.Cdotypeid" & vbCrLf & _
                    "INNER JOIN Spec ON CStep.Specid = Spec.Specid" & vbCrLf & _
                    "INNER JOIN Specbase ON Specbase.Specbaseid = Spec.Specbaseid" & vbCrLf & _
                    "INNER JOIN Container ON Container.ContainerID = Historymainline.ContainerID" & vbCrLf & _
                    "left JOIN cdodefinition cdo1 ON Historymainline.txntype = cdo1.cdodefid " & vbCrLf & _
                    "WHERE Historymainline.reversalstatus = 1 AND Cdodefinition.Cdoname = 'Workflow'  AND cdo1.Cdoname='MoveStd'"

        Dim dtOper As DataTable = dal.GetContainerInfo(sql)



        sql = "SELECT DISTINCT o.mfgordername,c.containername,c.containerid,pb.productname,c.productid,p.description," & vbCrLf & _
                            "   p.materialspecification,p.materialsize,p.MaterialTrademark,p.materialstatus,p.materialname,p.technicalconditions," & vbCrLf & _
                            "   c.qty,materialinfo.furnacenumber,materialinfo.materialinfoname,'' as planname,'' jiaweinum " & vbCrLf & _
                            "FROM container c" & vbCrLf & _
                            "LEFT JOIN containerlevel cl ON cl.containerlevelid = c.levelid " & vbCrLf & _
                            "LEFT JOIN mfgorder o ON o.mfgorderid = c.mfgorderid " & vbCrLf & _
                            "LEFT JOIN inventtaskinfo task ON task.inventtaskinfoid = o.inventtaskinfoid " & vbCrLf & _
                            "LEFT JOIN uom u ON u.uomid = c.uomid " & vbCrLf & _
                            "LEFT JOIN prioritycode pr ON pr.prioritycodeid = c.prioritycodeid " & vbCrLf & _
                            "LEFT JOIN product p ON p.productid = c.productid " & vbCrLf & _
                            "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid " & vbCrLf & _
                            "LEFT JOIN materialinfo ON materialinfo.Qualificationnum=c.qualificationnum " & vbCrLf & _
                            "LEFT JOIN historymainline on historymainline.containerid=c.containerid " & vbCrLf & _
                            "LEFT JOIN employee on historymainline.employeeid=employee.employeeid " & vbCrLf & _
                            "LEFT JOIN currentstatus cts ON cts.currentstatusid = c.currentstatusid " & vbCrLf & _
                            "LEFT JOIN spec s ON s.specid = cts.specid " & vbCrLf & _
                            "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid " & vbCrLf & _
                            "LEFT JOIN workflowstep ws ON ws.workflowstepid = historymainline.workflowstepid  " & vbCrLf & _
                            "LEFT JOIN factory f ON f.factoryid = cts.factoryid " & vbCrLf & _
                            "LEFT JOIN cdodefinition cdo ON cdo.cdodefid = historymainline.txntype " & vbCrLf & _
                            "WHERE c.status = 1 AND c.containername <> c.containerid AND c.parentcontainerid IS NULL " & vbCrLf & _
                            " AND (ws.workflowstepname='物料检验（下料后）' or ws.workflowstepname='工段长接收' or ws.workflowstepname='领料') and cdo.cdoname='MoveStd'  and o.mfgordername is not NULL and Historymainline.reversalstatus = 1 " & vbCrLf

        If containerids <> "" Then
            sql += " and c.containerid in (" + containerids + ") "
        End If
        If dto.ProductName <> "" Then
            sql += " and pb.productname like '%" + dto.ProductName + "%' "
        End If
        sql += "ORDER BY c.containername DESC" & vbCrLf

        'Dim DT As DataTable = dal.GetContainerInfo(sql)

        Dim para As uMESPagingDataDTO = New uMESPagingDataDTO()
        para.CurrentPageIndex = PageIndex
        para.PageSize = PageSize
        para.strSQL = sql
        GetMaterialXialiaoList = dal.RunProcedureForPage(para)

        'DT.Columns.Add("shicainame")
        'DT.Columns.Add("shipaihao")
        'DT.Columns.Add("shiguige")
        'DT.Columns.Add("shistatus")
        'DT.Columns.Add("shijishucodition")
        'DT.Columns.Add("shisize")
        'DT.Columns.Add("shiqty")

        GetMaterialXialiaoList.DBTable.Columns.Add("baoguan")
        GetMaterialXialiaoList.DBTable.Columns.Add("checkman")
        GetMaterialXialiaoList.DBTable.Columns.Add("lingliaoman")
        GetMaterialXialiaoList.DBTable.Columns.Add("xialiaoman")
        For i = 0 To GetMaterialXialiaoList.DBTable.Rows.Count - 1
            Dim shicainame As String = GetMaterialDatapointValue(GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid"), "名称")
            If shicainame <> "" Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("materialname") = shicainame
            End If

            Dim shipaihao As String = GetMaterialDatapointValue(GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid"), "可供牌号")
            If shipaihao <> "" Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("MaterialTrademark") = shipaihao
            End If

            Dim shiguige As String = GetMaterialDatapointValue(GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid"), "可供规格")
            If shiguige <> "" Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("materialspecification") = shiguige
            End If

            Dim shistatus As String = GetMaterialDatapointValue(GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid"), "状态(冷、热)")
            If shistatus <> "" Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("materialstatus") = shistatus
            End If

            Dim shijishucodition As String = GetMaterialDatapointValue(GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid"), "技术条件")
            If shijishucodition <> "" Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("technicalconditions") = shijishucodition
            End If

            Dim shisize As String = GetMaterialDatapointValue(GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid"), "毛坯尺寸")
            If shisize <> "" Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("materialsize") = shisize
            End If

            Dim shiqty As String = GetMaterialDatapointValue(GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid"), "可供数量")
            If shiqty <> "" Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("qty") = shiqty
            End If

            Dim jiaweinum As String = GetMaterialDatapointValue(GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid"), "架位号")
            If jiaweinum <> "" Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("jiaweinum") = jiaweinum
            End If

            Dim lupihao As String = GetMaterialDatapointValue(GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid"), "炉批号")
            If lupihao <> "" Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("furnacenumber") = lupihao
            End If

            Dim ruchangfujianhao As String = GetMaterialDatapointValue(GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid"), "入厂复检号")
            If ruchangfujianhao <> "" Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("materialinfoname") = ruchangfujianhao
            End If

            Dim row As DataRow()

            row = dtOper.Select("Containerid='" + GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid") + "' and Specname='物料审核'")
            If row.Length > 0 Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("planname") = row(0).Item("FullName").ToString()
            End If

            row = dtOper.Select("Containerid='" + GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid") + "' and Specname='物料备料'")
            If row.Length > 0 Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("baoguan") = row(0).Item("FullName").ToString()
            End If

            row = dtOper.Select("Containerid='" + GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid") + "' and Specname='物料检验（下料后）'")
            If row.Length > 0 Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("checkman") = row(0).Item("FullName").ToString()
            End If

            row = dtOper.Select("Containerid='" + GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid") + "' and Specname='物料申请'")
            If row.Length > 0 Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("lingliaoman") = row(0).Item("FullName").ToString()
            End If

            row = dtOper.Select("Containerid='" + GetMaterialXialiaoList.DBTable.Rows(i).Item("containerid") + "' and Specname='物料下料'")
            If row.Length > 0 Then
                GetMaterialXialiaoList.DBTable.Rows(i).Item("xialiaoman") = row(0).Item("FullName").ToString()
            End If


        Next
    End Function


    '获取待打印的下料单相应跟踪卡操作人员（保管员，下料工，车间领料员，检验员）'
    Public Function GetMaterialStepOperator(ByVal containerid As String, ByRef specname As String) As DataTable
        Dim sql As String = "SELECT DISTINCT Historymainline.Containerid,Container.Containername,Specbase.Specname, Employee.FullName,cdo1.cdoname,Historymainline.Txndate,Historymainline.Historymainlineid" & vbCrLf & _
                    "FROM Movehistory " & vbCrLf & _
                    "INNER JOIN Historymainline ON Movehistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                    "INNER JOIN" & vbCrLf & _
                    "(" & vbCrLf & _
                     "   SELECT Historymainline.Containerid, Historymainline.Workflowstepid, MAX(Historymainline.Txndate) AS Txndate" & vbCrLf & _
                    "    FROM Movehistory " & vbCrLf & _
                     "   INNER JOIN Historymainline ON Movehistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                     "   INNER JOIN Workflowstep ON Historymainline.Workflowstepid = Workflowstep.Workflowstepid" & vbCrLf & _
                     "   INNER JOIN" & vbCrLf & _
                      "  (" & vbCrLf & _
                     "       SELECT Container.Containerid, (CASE WHEN Cdodefinition.Cdoname = 'MachineMfgWorkFlow' THEN 9999 ELSE Workflowstep.Sequence END) AS SpecIndex" & vbCrLf & _
                      "      FROM Container" & vbCrLf & _
                       "     INNER JOIN Currentstatus ON Container.Currentstatusid= Currentstatus.Currentstatusid" & vbCrLf & _
                       "     INNER JOIN Workflowstep ON Currentstatus.Workflowstepid = Workflowstep.Workflowstepid" & vbCrLf & _
                       "     INNER JOIN Workflow ON Workflow.Workflowid = Workflowstep.Workflowid" & vbCrLf & _
                       "     INNER JOIN Cdodefinition ON Workflow.Cdotypeid = Cdodefinition.Cdodefid" & vbCrLf & _
                      "  ) CSpec ON CSpec.Containerid= Historymainline.ContainerID AND CSpec.SpecIndex >= Workflowstep.Sequence" & vbCrLf & _
                      "  WHERE Historymainline.reversalstatus = 1 " & vbCrLf & _
                     "   GROUP BY Historymainline.Containerid, Historymainline.Workflowstepid" & vbCrLf & _
                    ") CMax ON CMax.Containerid = Historymainline.Containerid AND CMax.Workflowstepid = Historymainline.Workflowstepid AND CMax.Txndate = Historymainline.Txndate" & vbCrLf & _
                    "INNER JOIN Employee ON Historymainline.Employeeid = Employee.Employeeid" & vbCrLf & _
                    "INNER JOIN" & vbCrLf & _
                    "(" & vbCrLf & _
                    "    SELECT Workflowstep.Workflowid, Workflowstep.Workflowstepid, " & vbCrLf & _
                    "           (CASE WHEN Workflowstep.Specid = '0000000000000000' THEN Specbase.Revofrcdid ELSE Spec.Specid END) AS Specid" & vbCrLf & _
                    "    FROM Workflowstep " & vbCrLf & _
                     "   LEFT OUTER JOIN Spec ON Workflowstep.Specid = Spec.Specid" & vbCrLf & _
                     "   LEFT OUTER JOIN Specbase ON Specbase.Specbaseid = Workflowstep.Specbaseid" & vbCrLf & _
                    ") CStep ON Movehistory.Stepid = CStep.Workflowstepid" & vbCrLf & _
                    "INNER JOIN Workflow ON Workflow.Workflowid = CStep.Workflowid" & vbCrLf & _
                    "INNER JOIN Cdodefinition ON Cdodefinition.Cdodefid = Workflow.Cdotypeid" & vbCrLf & _
                    "INNER JOIN Spec ON CStep.Specid = Spec.Specid" & vbCrLf & _
                    "INNER JOIN Specbase ON Specbase.Specbaseid = Spec.Specbaseid" & vbCrLf & _
                    "INNER JOIN Container ON Container.ContainerID = Historymainline.ContainerID" & vbCrLf & _
                    "left JOIN cdodefinition cdo1 ON Historymainline.txntype = cdo1.cdodefid " & vbCrLf & _
                    "WHERE Historymainline.reversalstatus = 1 AND Cdodefinition.Cdoname = 'Workflow' AND Specbase.Specname='" + specname + "' AND cdo1.Cdoname='MoveStd'" & vbCrLf & _
                    "and Container.Containerid='" + containerid + "' "

        Dim DT As DataTable = dal.GetContainerInfo(sql)
        Return DT
    End Function

    '获取待打印的下料单相应字段的数据采集值（）'
    Public Function GetMaterialDatapointValue(ByVal containerid As String, ByRef dataname As String) As String
        Dim result As String = ""
        Dim sql As String = "select datapointhistorydetail.dataname,datapointhistorydetail.datavalue " & vbCrLf & _
                    "from  datapointhistorydetail " & vbCrLf & _
                    "inner join datapointhistory on datapointhistory.datapointhistoryid=datapointhistorydetail.datapointhistoryid " & vbCrLf & _
                    "inner join historymainline on historymainline.historymainlineid=datapointhistory.historymainlineid " & vbCrLf & _
                    "inner join container on container.containerid=historymainline.containerid " & vbCrLf & _
                     "where datapointhistorydetail.dataname='" + dataname + "' and container.containerid='" + containerid + "' and rownum=1 " & vbCrLf & _
                    "order by historymainline.txndate desc" & vbCrLf
        Dim DT As DataTable = dal.GetContainerInfo(sql)
        If DT.Rows.Count > 0 Then
            result = DT.Rows(0).Item("datavalue").ToString()
        End If
        Return result
    End Function


    '获取待打印的出库单列表'
    Public Function GetMaterialOutList(ByVal dto As MaterialParamterDto, ByVal PageIndex As Integer, ByVal PageSize As Integer, ByRef containerids As String) As uMESProcessResult

        Dim sql As String = "SELECT DISTINCT Historymainline.Containerid,Container.Containername,Specbase.Specname, Employee.FullName,cdo1.cdoname,Historymainline.Txndate,Historymainline.Historymainlineid" & vbCrLf & _
                     "FROM Movehistory " & vbCrLf & _
                     "INNER JOIN Historymainline ON Movehistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                     "INNER JOIN" & vbCrLf & _
                     "(" & vbCrLf & _
                      "   SELECT Historymainline.Containerid, Historymainline.Workflowstepid, MAX(Historymainline.Txndate) AS Txndate" & vbCrLf & _
                     "    FROM Movehistory " & vbCrLf & _
                      "   INNER JOIN Historymainline ON Movehistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                      "   INNER JOIN Workflowstep ON Historymainline.Workflowstepid = Workflowstep.Workflowstepid" & vbCrLf & _
                      "   INNER JOIN" & vbCrLf & _
                       "  (" & vbCrLf & _
                      "       SELECT Container.Containerid, (CASE WHEN Cdodefinition.Cdoname = 'MachineMfgWorkFlow' THEN 9999 ELSE Workflowstep.Sequence END) AS SpecIndex" & vbCrLf & _
                       "      FROM Container" & vbCrLf & _
                        "     INNER JOIN Currentstatus ON Container.Currentstatusid= Currentstatus.Currentstatusid" & vbCrLf & _
                        "     INNER JOIN Workflowstep ON Currentstatus.Workflowstepid = Workflowstep.Workflowstepid" & vbCrLf & _
                        "     INNER JOIN Workflow ON Workflow.Workflowid = Workflowstep.Workflowid" & vbCrLf & _
                        "     INNER JOIN Cdodefinition ON Workflow.Cdotypeid = Cdodefinition.Cdodefid" & vbCrLf & _
                       "  ) CSpec ON CSpec.Containerid= Historymainline.ContainerID AND CSpec.SpecIndex >= Workflowstep.Sequence" & vbCrLf & _
                       "  WHERE Historymainline.reversalstatus = 1 " & vbCrLf & _
                      "   GROUP BY Historymainline.Containerid, Historymainline.Workflowstepid" & vbCrLf & _
                     ") CMax ON CMax.Containerid = Historymainline.Containerid AND CMax.Workflowstepid = Historymainline.Workflowstepid AND CMax.Txndate = Historymainline.Txndate" & vbCrLf & _
                     "INNER JOIN Employee ON Historymainline.Employeeid = Employee.Employeeid" & vbCrLf & _
                     "INNER JOIN" & vbCrLf & _
                     "(" & vbCrLf & _
                     "    SELECT Workflowstep.Workflowid, Workflowstep.Workflowstepid, " & vbCrLf & _
                     "           (CASE WHEN Workflowstep.Specid = '0000000000000000' THEN Specbase.Revofrcdid ELSE Spec.Specid END) AS Specid" & vbCrLf & _
                     "    FROM Workflowstep " & vbCrLf & _
                      "   LEFT OUTER JOIN Spec ON Workflowstep.Specid = Spec.Specid" & vbCrLf & _
                      "   LEFT OUTER JOIN Specbase ON Specbase.Specbaseid = Workflowstep.Specbaseid" & vbCrLf & _
                     ") CStep ON Movehistory.Stepid = CStep.Workflowstepid" & vbCrLf & _
                     "INNER JOIN Workflow ON Workflow.Workflowid = CStep.Workflowid" & vbCrLf & _
                     "INNER JOIN Cdodefinition ON Cdodefinition.Cdodefid = Workflow.Cdotypeid" & vbCrLf & _
                     "INNER JOIN Spec ON CStep.Specid = Spec.Specid" & vbCrLf & _
                     "INNER JOIN Specbase ON Specbase.Specbaseid = Spec.Specbaseid" & vbCrLf & _
                     "INNER JOIN Container ON Container.ContainerID = Historymainline.ContainerID" & vbCrLf & _
                     "left JOIN cdodefinition cdo1 ON Historymainline.txntype = cdo1.cdodefid " & vbCrLf & _
                     "WHERE Historymainline.reversalstatus = 1 AND Cdodefinition.Cdoname = 'Workflow'  AND cdo1.Cdoname='MoveStd'"

        Dim dtOper As DataTable = dal.GetContainerInfo(sql)



        sql = "SELECT DISTINCT o.mfgordername,c.containername,c.containerid,pb.productname,c.productid,p.description," & vbCrLf & _
                          "   p.materialspecification,p.materialsize,p.MaterialTrademark,p.materialstatus,p.materialname,p.technicalconditions," & vbCrLf & _
                          "   c.qty,materialinfo.furnacenumber,materialinfo.materialinfoname,'' as planname,'' jiaweinum " & vbCrLf & _
                          "FROM container c" & vbCrLf & _
                          "LEFT JOIN containerlevel cl ON cl.containerlevelid = c.levelid " & vbCrLf & _
                          "LEFT JOIN mfgorder o ON o.mfgorderid = c.mfgorderid " & vbCrLf & _
                          "LEFT JOIN inventtaskinfo task ON task.inventtaskinfoid = o.inventtaskinfoid " & vbCrLf & _
                          "LEFT JOIN uom u ON u.uomid = c.uomid " & vbCrLf & _
                          "LEFT JOIN prioritycode pr ON pr.prioritycodeid = c.prioritycodeid " & vbCrLf & _
                          "LEFT JOIN product p ON p.productid = c.productid " & vbCrLf & _
                          "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid " & vbCrLf & _
                          "LEFT JOIN materialinfo ON materialinfo.Qualificationnum=c.qualificationnum " & vbCrLf & _
                          "LEFT JOIN historymainline on historymainline.containerid=c.containerid " & vbCrLf & _
                          "LEFT JOIN employee on historymainline.employeeid=employee.employeeid " & vbCrLf & _
                          "LEFT JOIN currentstatus cts ON cts.currentstatusid = c.currentstatusid " & vbCrLf & _
                          "LEFT JOIN spec s ON s.specid = cts.specid " & vbCrLf & _
                          "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid " & vbCrLf & _
                          "LEFT JOIN workflowstep ws ON ws.workflowstepid = historymainline.workflowstepid  " & vbCrLf & _
                          "LEFT JOIN factory f ON f.factoryid = cts.factoryid " & vbCrLf & _
                          "LEFT JOIN cdodefinition cdo ON cdo.cdodefid = historymainline.txntype " & vbCrLf & _
                          "WHERE c.status = 1 AND c.containername <> c.containerid AND c.parentcontainerid IS NULL " & vbCrLf & _
                          " AND (ws.workflowstepname='物料检验（下料后）' or ws.workflowstepname='工段长接收' or ws.workflowstepname='领料') and cdo.cdoname='MoveStd'  and o.mfgordername is not NULL and Historymainline.reversalstatus = 1 " & vbCrLf


        If containerids <> "" Then
            sql += " and c.containerid in (" + containerids + ") "
        End If
        If dto.ProductName <> "" Then
            sql += " and pb.productname like '%" + dto.ProductName + "%' "
        End If
        'If dto.strEmployeeName <> "" Then
        '    sql += " and c.containerid in (" + containerids + ") "
        'End If
        sql += "ORDER BY c.containername DESC" & vbCrLf

        'Dim DT As DataTable = dal.GetContainerInfo(sql)

        Dim para As uMESPagingDataDTO = New uMESPagingDataDTO()
        para.CurrentPageIndex = PageIndex
        para.PageSize = PageSize
        para.strSQL = sql
        GetMaterialOutList = dal.RunProcedureForPage(para)

        'DT.Columns.Add("shicainame")
        'DT.Columns.Add("shipaihao")
        'DT.Columns.Add("shiguige")
        'DT.Columns.Add("shistatus")
        'DT.Columns.Add("shijishucodition")
        'DT.Columns.Add("shisize")
        'DT.Columns.Add("shiqty")

        GetMaterialOutList.DBTable.Columns.Add("baoguan")
        GetMaterialOutList.DBTable.Columns.Add("checkman")
        GetMaterialOutList.DBTable.Columns.Add("lingliaoman")
        GetMaterialOutList.DBTable.Columns.Add("xialiaoman")
        For i = 0 To GetMaterialOutList.DBTable.Rows.Count - 1
            Dim shicainame As String = GetMaterialDatapointValue(GetMaterialOutList.DBTable.Rows(i).Item("containerid"), "名称")
            If shicainame <> "" Then
                GetMaterialOutList.DBTable.Rows(i).Item("materialname") = shicainame
            End If

            Dim shipaihao As String = GetMaterialDatapointValue(GetMaterialOutList.DBTable.Rows(i).Item("containerid"), "可供牌号")
            If shipaihao <> "" Then
                GetMaterialOutList.DBTable.Rows(i).Item("MaterialTrademark") = shipaihao
            End If

            Dim shiguige As String = GetMaterialDatapointValue(GetMaterialOutList.DBTable.Rows(i).Item("containerid"), "可供规格")
            If shiguige <> "" Then
                GetMaterialOutList.DBTable.Rows(i).Item("materialspecification") = shiguige
            End If

            Dim shistatus As String = GetMaterialDatapointValue(GetMaterialOutList.DBTable.Rows(i).Item("containerid"), "状态(冷、热)")
            If shistatus <> "" Then
                GetMaterialOutList.DBTable.Rows(i).Item("materialstatus") = shistatus
            End If

            Dim shijishucodition As String = GetMaterialDatapointValue(GetMaterialOutList.DBTable.Rows(i).Item("containerid"), "技术条件")
            If shijishucodition <> "" Then
                GetMaterialOutList.DBTable.Rows(i).Item("technicalconditions") = shijishucodition
            End If

            Dim shisize As String = GetMaterialDatapointValue(GetMaterialOutList.DBTable.Rows(i).Item("containerid"), "毛坯尺寸")
            If shisize <> "" Then
                GetMaterialOutList.DBTable.Rows(i).Item("materialsize") = shisize
            End If

            Dim shiqty As String = GetMaterialDatapointValue(GetMaterialOutList.DBTable.Rows(i).Item("containerid"), "实供数量")
            If shiqty <> "" Then
                GetMaterialOutList.DBTable.Rows(i).Item("qty") = shiqty
            End If

            Dim jiaweinum As String = GetMaterialDatapointValue(GetMaterialOutList.DBTable.Rows(i).Item("containerid"), "架位号")
            If jiaweinum <> "" Then
                GetMaterialOutList.DBTable.Rows(i).Item("jiaweinum") = jiaweinum
            End If

            Dim lupihao As String = GetMaterialDatapointValue(GetMaterialOutList.DBTable.Rows(i).Item("containerid"), "炉批号")
            If lupihao <> "" Then
                GetMaterialOutList.DBTable.Rows(i).Item("furnacenumber") = lupihao
            End If

            Dim ruchangfujianhao As String = GetMaterialDatapointValue(GetMaterialOutList.DBTable.Rows(i).Item("containerid"), "入厂复检号")
            If ruchangfujianhao <> "" Then
                GetMaterialOutList.DBTable.Rows(i).Item("materialinfoname") = ruchangfujianhao
            End If

            Dim row As DataRow()

            row = dtOper.Select("Containerid='" + GetMaterialOutList.DBTable.Rows(i).Item("containerid") + "' and Specname='物料审核'")
            If row.Length > 0 Then
                GetMaterialOutList.DBTable.Rows(i).Item("planname") = row(0).Item("FullName").ToString()
            End If

            row = dtOper.Select("Containerid='" + GetMaterialOutList.DBTable.Rows(i).Item("containerid") + "' and Specname='物料备料'")
            If row.Length > 0 Then
                GetMaterialOutList.DBTable.Rows(i).Item("baoguan") = row(0).Item("FullName").ToString()
            End If

            row = dtOper.Select("Containerid='" + GetMaterialOutList.DBTable.Rows(i).Item("containerid") + "' and Specname='物料检验（下料后）'")
            If row.Length > 0 Then
                GetMaterialOutList.DBTable.Rows(i).Item("checkman") = row(0).Item("FullName").ToString()
            End If

            row = dtOper.Select("Containerid='" + GetMaterialOutList.DBTable.Rows(i).Item("containerid") + "' and Specname='物料申请'")
            If row.Length > 0 Then
                GetMaterialOutList.DBTable.Rows(i).Item("lingliaoman") = row(0).Item("FullName").ToString()
            End If

            row = dtOper.Select("Containerid='" + GetMaterialOutList.DBTable.Rows(i).Item("containerid") + "' and Specname='物料下料'")
            If row.Length > 0 Then
                GetMaterialOutList.DBTable.Rows(i).Item("xialiaoman") = row(0).Item("FullName").ToString()
            End If
        Next
    End Function

    '获取待打印的派工单列表
    Public Function GetWorkCenterDispatchingList(ByVal dto As MaterialParamterDto, ByVal PageIndex As Integer, ByVal PageSize As Integer, ByRef containerids As String) As uMESProcessResult

        Dim sql As String = "select distinct WorkCenterDispatching.Workcenterdispatchingid,WorkCenterDispatching.Qty,WorkCenterDispatching.Reportedqty,WorkCenterDispatching.Notes," & vbCrLf & _
                            "mfgorder.mfgordername,container.containerid,container.containername,'' as factoryname,NULL as completedate,productbase.productname," & vbCrLf & _
                            "product.description,'' as cailiao,'' as recontainername,CSpec.Sequence,CSpec.Workflowstepid,CSpec.Workflowstepname " & vbCrLf & _
                            "from WorkCenterDispatching " & vbCrLf & _
                            "inner join container on container.containerid=WorkCenterDispatching.Containerid " & vbCrLf & _
                            "left join Workingreporthistory on Workingreporthistory.Workcenterdispatchingid=WorkCenterDispatching.Workcenterdispatchingid " & vbCrLf & _
                            "left join historymainline on historymainline.historymainlineid=Workingreporthistory.Historymainlineid " & vbCrLf & _
                            "left join product on product.productid=container.productid " & vbCrLf & _
                            "left join productbase on productbase.productbaseid=product.productbaseid " & vbCrLf & _
                            "left join mfgorder on mfgorder.mfgorderid=container.mfgorderid " & vbCrLf & _
                            "left join inventtaskinfo  ON inventtaskinfo.inventtaskinfoid = mfgorder.inventtaskinfoid " & vbCrLf & _
                            "Left  Join " & vbCrLf & _
                            "( " & vbCrLf & _
                            "    SELECT  Workflowstep.Sequence,Workflowstep.Workflowstepid,Workflowstep.Workflowstepname, Workflowstep.Workflowid," & vbCrLf & _
                            "    (CASE WHEN Workflowstep.Specid = '0000000000000000' " & vbCrLf & _
                            "    THEN Specbase.Revofrcdid ELSE Workflowstep.Specid END) AS Specid " & vbCrLf & _
                            "    FROM Workflowstep " & vbCrLf & _
                            "    LEFT JOIN Specbase ON Specbase.Specbaseid = Workflowstep.Specbaseid " & vbCrLf & _
                            ") CSpec ON CSpec.Workflowstepid = Historymainline.Workflowstepid " & vbCrLf & _
                            "where mfgorder.Mfgordername is not NULL " & vbCrLf

        If containerids <> "" Then
            sql += " and container.containerid in (" + containerids + ") "
        End If
        sql += "group by container.containerid,container.containername,mfgorder.mfgordername,productbase.productname,product.description," & vbCrLf & _
                "WorkCenterDispatching.Workcenterdispatchingid,WorkCenterDispatching.Workcenterdispatchingid,WorkCenterDispatching.Qty,WorkCenterDispatching.Reportedqty," & vbCrLf & _
                "WorkCenterDispatching.Notes,CSpec.Sequence,CSpec.Workflowstepid,CSpec.Workflowstepname " & vbCrLf & _
                "order by container.containerid desc,CSpec.Sequence,CSpec.Workflowstepid asc " & vbCrLf

        Dim dtOper As DataTable = dal.GetContainerInfo(sql)

        Dim para As uMESPagingDataDTO = New uMESPagingDataDTO()
        para.CurrentPageIndex = PageIndex
        para.PageSize = PageSize
        para.strSQL = sql
        GetWorkCenterDispatchingList = dal.RunProcedureForPage(para)
    End Function

    ''' <summary>
    ''' zll 20151028 添加：获得工艺路线的BOM件号列表
    ''' </summary>
    ''' <param name="workflowname"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetProductListByWorkFlow(ByVal workflowname As String) As DataTable
        Dim result As String = ""
        Dim sql As String = "select wfb.workflowname,wf.workflowrevision,bb.bomname,pb1.productname from workflow wf " & vbCrLf & _
                    "left join workflowbase wfb on wfb.workflowbaseid=wf.workflowbaseid " & vbCrLf & _
                    "left join product p on (p.workflowid=wf.workflowid and p.workflowbaseid='0000000000000000')  " & vbCrLf & _
                    "or (p.workflowbaseid=wf.workflowbaseid and p.workflowid='0000000000000000') " & vbCrLf & _
                    "left join productbase pb on pb.productbaseid=p.productbaseid " & vbCrLf & _
                    "left join productmateriallistitem pml on (pml.productid=p.productid and pml.productbaseid='0000000000000000') " & vbCrLf & _
                    "or (pml.productbaseid=p.productbaseid and pml.productid='0000000000000000')  " & vbCrLf & _
                    "left join bom b on b.bomid=pml.bomid " & vbCrLf & _
                    "left join bombase bb on bb.bombaseid=b.bombaseid " & vbCrLf & _
                    "left join product p1 on (p1.bomid=b.bomid and p1.bombaseid='0000000000000000') or (p1.bombaseid=b.bombaseid and p1.bomid='0000000000000000')  " & vbCrLf & _
                    "left join productbase pb1 on pb1.productbaseid=p1.productbaseid" & vbCrLf & _
                     "where pb1.productname is not null and wfb.workflowname='" & workflowname & "'" & vbCrLf
        Dim DT As DataTable = dal.StandardGetDataTable(sql)
        Return DT
    End Function

    Public Function GetMfgWorkflow(ByVal strContainerName As String) As DataTable
        Dim sql As String = String.Format("select c.containername, c.mfgworkflowid,w.workflowrevision,wb.workflowname,wt.workflowstepname,wt.sequence ,wt.workflowstepid," & vbCrLf & _
                                         "wt1.workflowstepname as currstepname ,l.containerlevelname from container c " & vbCrLf & _
                                         "left join workflow w on w.workflowid=c.mfgworkflowid " & vbCrLf & _
                                         "left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid " & vbCrLf & _
                                         "left join workflowstep wt on wt.workflowid=c.mfgworkflowid " & vbCrLf & _
                                         "left join currentstatus cs on cs.currentstatusid=c.currentstatusid " & vbCrLf & _
                                         "left join workflowstep wt1 on wt1.workflowstepid=cs.workflowstepid " & vbCrLf & _
                                         "left join containerlevel l on l.containerlevelid=c.levelid " & vbCrLf & _
                                         "where wt.sequence=1 and c.containername='{0}'", strContainerName)
        Dim DT As DataTable = dal.StandardGetDataTable(sql)
        Return DT
    End Function

End Class
